﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using DevExpress.Web.Mvc;
using Webcar.BLL;
using Webcar.Entitys;
using Webcar.Utils;
using System.Data.Entity;
using System.Web.UI.WebControls;
using System.Threading;
using System.Data;

namespace Webcar.Controllers
{
	public partial class CarController
	{
        public ActionResult Summary(string summaryType)
        {

            var model = GetData(summaryType);
            if (model == null) return View();
            return View(model:model);
        }

        DataTable GetData(string summaryType)
        {
            DataTable model = null;
            if (summaryType == "1")
                model = GetCarSortData();
            if (summaryType == "2")
                model = GetAssdeptData();
            if (summaryType == "3")
                model = GetCarUseData();
            return model;
        }
        string GetCaption(string summaryType)
        {
            if (summaryType == "1")
                return "按车型统计";
            if (summaryType == "2")
                return "按资产归属统计";
            if (summaryType == "3")
                return "按车辆用途统计";
            return "按车型统计";
        }
        public ActionResult ExportSummary(string summaryType)
        {
            var table = GetData(summaryType);
            GridViewSettings settings = new GridViewSettings();
            settings.Name = "gridviewsetting";
            foreach (DataColumn col in table.Columns)
            {
                settings.Columns.Add(col.ColumnName, col.Caption);
            }
            return GridViewExtension.ExportToXls(settings, table, GetCaption(summaryType));
        }

        class QueryGroupResult
        {
            public string dept_code
            {
                get;
                set;
            }
            public string col
            {
                get;
                set;
            }
            public int nums
            {
                get;
                set;
            }

        }
        DataTable GetCarUseData()
        {
            string sql = @"select a.dept_code,isnull(b.base_name,'无') col,count(1) nums from bs_car_record a left join 
						v_car_use b
						 on a.CAR_USE = b.base_code 
						join sys_dept c on a.dept_code=c.deptcode and c.use_flag='T'
						where a.is_use='T' 
						group by a.dept_code,b.base_name";
            var select_result = WebcarDataProvider.DB.ExecuteDataTable(sql).Rows.Cast<DataRow>().Select(a =>
                new QueryGroupResult()
                {
                    dept_code = a["dept_code"].ToString(),
                    col = a["col"].ToString(),
                    nums = a["nums"].ToString().ToInt()
                }
                );
            return GetData(select_result);
        }
        DataTable GetAssdeptData()
        {
            string sql = @"select a.dept_code,isnull(b.base_name,'无') col,count(1) nums from bs_car_record a left join 
						v_assept_kind b
						 on a.asset_dept = b.base_code 
						join sys_dept c on a.dept_code=c.deptcode and c.use_flag='T'
						where a.is_use='T' 
						group by a.dept_code,b.base_name";
            var select_result = WebcarDataProvider.DB.ExecuteDataTable(sql).Rows.Cast<DataRow>().Select(a =>
                new QueryGroupResult()
                {
                    dept_code = a["dept_code"].ToString(),
                    col = a["col"].ToString(),
                    nums = a["nums"].ToString().ToInt()
                }
                );
            return GetData(select_result);
        }
        DataTable GetCarSortData()
        {
            string sql = @"select a.dept_code,isnull(b.base_name,'无') col,count(1) nums from bs_car_record a left join V_BS_CAR_SORT b
						 on a.car_sort = b.base_code
						join sys_dept c on a.dept_code=c.deptcode and c.use_flag='T'
						 where  a.is_use='T'
						group by a.dept_code,b.base_name";
            var select_result = WebcarDataProvider.DB.ExecuteDataTable(sql).Rows.Cast<DataRow>().Select(a =>
                new QueryGroupResult()
                {
                    dept_code = a["dept_code"].ToString(),
                    col = a["col"].ToString(),
                    nums = a["nums"].ToString().ToInt()
                }
                );
            return GetData(select_result);
        }

        private DataTable GetData(IEnumerable<QueryGroupResult> select_result)
        {
            DataTable table = new DataTable();
            var col_result = select_result.Distinct(a=>a.col);
            table.Columns.Add("部门名称");
            foreach (var result in col_result)
            {
                table.Columns.Add(result.col.ToString(),typeof(int));
            }
            //string exp = col_result.Select(a=>a.col).Join("+");
            table.Columns.Add("合计", typeof(int));
			var dept_list = from p in Webcar.Entitys.WebcarDataProvider.DB.sys_dept
                        where p.use_flag == "T" && p.super_dept==null
						select p;

            //string exp = col_result.Select(a => a.col).Join("+");
            foreach (var dept in dept_list)
            {
                DataRow row = table.NewRow();
                row["部门名称"] = dept.deptname;
                int numbers = 0;
                foreach (DataColumn table_col in table.Columns)
                {
                    if (table_col.ColumnName == "部门名称") continue;
                    if (table_col.ColumnName == "合计") continue;

                    var item = select_result.FirstOrDefault(a => a.dept_code == dept.deptcode && a.col == table_col.ColumnName);
                    if (item != null)
                    {
                        row[table_col.ColumnName] = item.nums;
                        numbers += item.nums;
                    }
                }
                row["合计"] = numbers;
                table.Rows.Add(row);
            }

            //合计
            DataRow sum_row = table.NewRow();
            sum_row["部门名称"] = "合计：";
            foreach (DataColumn column in table.Columns)
            {
                if (column.ColumnName == "部门名称") continue;
                sum_row[column.ColumnName] = table.Compute("sum(" + column.ColumnName + ")", "");
            }
            table.Rows.Add(sum_row);

            return table;
        }


	}
}